Excel BI - Excel Challenge 689

excel-challenges

excel-formulas

🔰 The task is to assign sequential numbers to the Result column based on the Number column.

Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 689

Challenge Description

🔰 The task is to assign sequential numbers to the Result column based on the Number column. Each ID has corresponding numbers. If the numbers for an ID are not consecutive, they should be marked as 1. However, if the numbers are consecutive, they should be marked according to their count. For example, ID 1000008 has numbers 5, 7, 15, 16; since 5 and 7 are not consecutive, they would each be marked as 1. But since 15 and 16 are consecutive, they would be marked as 2, which is the correct result.

Solutions

library(tidyverse)
library(readxl)

path = "Excel/689 Consecutive Numbers Marking.xlsx"
input = read_excel(path, range = "A1:B29")
test  = read_excel(path, range = "D1:D29")

result = input %>%
  mutate(rn = row_number()) %>%
  group_by(
    group_id = cumsum(ID != lag(ID, default = first(ID)) | 
                        (Number - rn) != lag(Number - rn, default = first(Number - rn)))
  ) %>%
  mutate(answer_expected = n()) %>%
  ungroup() %>%
  select(answer_expected)

all.equal(result$answer_expected, test$`Answer Expected`)
# TRUE

Logic: Read the workbook ranges needed for the challenge; Derive the required intermediate columns; Aggregate or rank the data at the required grouping level.
Strengths: The transformation is organized around the correct grouping level, which keeps the business logic clear.
Areas for Improvement: The solution assumes the workbook layout and selected ranges remain stable, so any structural change in the sheet would require small adjustments.
Gem: The key move is solving the problem at the right grain before shaping the final output.

import pandas as pd

path = "689 Consecutive Numbers Marking.xlsx"
input = pd.read_excel(path, usecols="A:B", nrows=29)
test = pd.read_excel(path, usecols="D", nrows=29)

input['rn'] = range(1, len(input) + 1)
input['group_id'] = (
    (input['ID'] != input['ID'].shift(1)) |
    ((input['Number'] - input['rn']) != (input['Number'] - input['rn']).shift(1))
).cumsum()
input['answer_expected'] = input.groupby('group_id')['group_id'].transform('size')
result = input[['answer_expected']]

print(result['answer_expected'].equals(test['Answer Expected']))

The Python version follows the same grouped logic and keeps the transformation explicit in a dataframe pipeline.

Difficulty Level

Medium

The individual steps are manageable, but the correct transformation pattern is not obvious from the raw data.